﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
using WoodManager.Header;

namespace WoodManager.BO
{
    class DeXuatVanBO
    {
        CommonDO cmDO = new CommonDO();

        public String loadDataGrids( ref DataTable tbDeXuatVan,
                                    ref DataTable tbTongHopVan,
                                    ref DataTable tbDinhMucSon,
                                    StructDeXuatVan stDXV,
                                    ref string sSoluong,
                                    ref string sKichThuoc)
        {
            String ret = CommonHeader.NO_ERROR;
            try
            {
                ConnectManager cm = new ConnectManager();
                String cmd = "";
                DataTable tbSoLuong = new DataTable();
                string cmdGetSoLuong = "Select SoLuong, Ngang, Cao, Sau from HangMucSanPham where HMSPID = N'";
                //Get Info of ViewDeXuatVan
                String cmdDeXuatVan = "Select * from ViewDeXuatVan Where ";

                //Get info of ViewThongKeVatTuSon
                String cmdThongKeVTSon = "Select * from ViewThongKeVatTuSon Where ";

                //Get Info of ViewTongHopVan
                String cmdTongHopVan = "Select * from ViewTongHopVan Where ";

                //Make condition
                if (stDXV.sDHID != "")
                {
                    cmd = "DHID = N'" + stDXV.sDHID + "' ";
                }
                if (stDXV.sHMSPID != "")
                {
                    cmd = cmd + " AND HMSPID = N'" + stDXV.sHMSPID + "' ";
                    cmdGetSoLuong = cmdGetSoLuong + stDXV.sHMSPID + "' "; 
                    tbSoLuong =  cm.getTable(cmdGetSoLuong);
                    if (tbSoLuong.Rows.Count == 1)
                    {
                        sSoluong = tbSoLuong.Rows[0][0].ToString();
                        sKichThuoc = tbSoLuong.Rows[0][1].ToString() + " X " +
                                        tbSoLuong.Rows[0][2].ToString() + " X " +
                                        tbSoLuong.Rows[0][3].ToString();
                    }
                    else
                    {
                        sSoluong = "N/A";
                        sKichThuoc = "N/A";
                    }
                }
                if (stDXV.sVersion != "")
                {
                    cmd = cmd + " AND Version = N'" + stDXV.sVersion + "' ";
                }
                if (stDXV.sThanhPhan != "")
                {
                    cmd = cmd + " AND ThanhPhan = N'" + stDXV.sThanhPhan + "' ";
                }
                
                tbDinhMucSon = cm.getTable(cmdThongKeVTSon + cmd);
                tbDeXuatVan = cm.getTable(cmdDeXuatVan+cmd);
                tbTongHopVan = cm.getTable(cmdTongHopVan+cmd);
            }
            catch (Exception ex)
            {
                return ex.Message;                
            }
            return ret;
        }

        public String loadVanLoi(SqlConnection conn, ref DataTable tbVanLoi)
        {
            String ret = CommonHeader.NO_ERROR;
            String cmd = SqlHeader.SP_GET_VAN_LOI;
            CommonDO cmDO = new CommonDO();
            ret = cmDO.getTable(conn, ref tbVanLoi, cmd);
            if (ret != CommonHeader.NO_ERROR)
            {
                return ret;
            }
            return ret;
        }


        public String loadSonPhu(SqlConnection conn, ref DataTable tbSonPhu, String sIDVan)
        {
            String cmd = "SELECT DISTINCT IDSon, TenSonPhu FROM ViewToHopVanSon WHERE IDVan = N'" + sIDVan + "'";
            return cmDO.getTable(conn, ref tbSonPhu, cmd);
        }

        public String loadChiCanh(SqlConnection conn, ref DataTable tbChiCanh)
        {
            String ret = CommonHeader.NO_ERROR;
            String cmd = "SELECT IDChiCanh, TenChiCanh FROM ChiCanh";
            ret = cmDO.getTable(conn, ref tbChiCanh, cmd);
            if (ret != CommonHeader.NO_ERROR)
            {
                return ret;
            }
            return ret;        
        }

        public String loadSoCanhSon(SqlConnection conn, ref DataTable tbSoCanh)
        {
            String ret = CommonHeader.NO_ERROR;
            String cmd = "SELECT ID, ChiTiet FROM SoCanhSon";
            //CommonDO cmDO = new CommonDO();
            ret = cmDO.getTable(conn, ref tbSoCanh, cmd);
            if (ret != CommonHeader.NO_ERROR)
            {
                return ret;
            }
            return ret;
        }

        public String updateRecord( SqlConnection conn,StructDeXuatVan stUpdate, StructDeXuatVan stBase )
        {
            String ret = CommonHeader.NO_ERROR;
            String cmd = SqlHeader.SP_UPDATE_DXV;
            cmd = String.Concat(cmd, " N'", stUpdate.sHMSPID , "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.sVersion, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.sMaLoiVan, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.sMaCHiTiet, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.sThanhPhan, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.sTenChiTiet, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.dDai, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.dRong, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.dSL, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.sPhuMat1, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.sPhuMat2, "'");
            cmd = String.Concat(cmd, " ,N'", stUpdate.sGhiChu, "'");
            cmd = String.Concat(cmd, " ,N'", stBase.sMaLoiVan, "'");
            cmd = String.Concat(cmd, " ,N'", stBase.sHMSPID, "'");
            cmd = String.Concat(cmd, " ,N'", stBase.sVersion, "'");
            cmd = String.Concat(cmd, " ,N'", stBase.sMaCHiTiet, "'");

            ret = cmDO.UpdateRecord(conn, cmd);
            if (ret != CommonHeader.NO_ERROR)
            {
                return ret;
            }
            return ret;            
        }

        public String insertRecordDeXuatVan(SqlConnection conn, StructDeXuatVan stDXV)
        {
            String ret = CommonHeader.NO_ERROR;
            String cmd = SqlHeader.SP_DE_XUAT_VAN_ADD;
            cmd = String.Concat(cmd, " N'" + stDXV.sHMSPID + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sVersion + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sMaLoiVan + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sMaCHiTiet + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sTenChiTiet + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sThanhPhan + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.dDai.ToString() + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.dRong.ToString() + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.dSL.ToString() + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sPhuMat1 + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sPhuMat2 + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sNVDeXuat + "',");
            cmd = String.Concat(cmd, " N'" + stDXV.sGhiChu + "'");
            
            //CommonDO cmDO = new CommonDO();
            ret = cmDO.InsertUpdateRecord(conn, cmd);
            if (ret != CommonHeader.NO_ERROR)
            {
                return ret;
            }
            return ret;               
        }

        public String deleteRecod(SqlConnection conn, StructDeXuatVan stDXV)
        {
            String ret = CommonHeader.NO_ERROR;
            String cmd = SqlHeader.SP_DELETE_ROW_DXV;
            cmd = String.Concat(cmd," N'" + stDXV.sHMSPID + "'");
            cmd = String.Concat(cmd, ", N'" + stDXV.sVersion + "'");
            cmd = String.Concat(cmd, ", N'" + stDXV.sMaLoiVan + "'");
            cmd = String.Concat(cmd, ", N'" + stDXV.sMaCHiTiet + "'");

            //CommonDO cmDO = new CommonDO();
            ret = cmDO.UpdateRecord(conn, cmd);
            if (ret != CommonHeader.NO_ERROR)
            {
                return ret;
            }
            return ret;                  
        }
    }

}
